"""
SQLAlchemy数据模型定义
基于SQLite数据库的9张核心表
"""

from datetime import datetime, date
from sqlalchemy import (
    Column, String, Integer, Float, Boolean, Date, DateTime, Text, 
    CheckConstraint, ForeignKey, UniqueConstraint
)
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from .database import Base

class Dictionary(Base):
    """字典表 - 提供所有模块的类型定义"""
    __tablename__ = 'DICTIONARY'
    
    dict_id = Column(String, primary_key=True)
    dict_type = Column(String, nullable=False)
    dict_code = Column(String, nullable=False)
    dict_name = Column(String, nullable=False)
    parent_code = Column(String)
    sort_order = Column(Integer, default=0)
    is_system = Column(Boolean, default=False)
    description = Column(Text)
    create_time = Column(DateTime, default=datetime.now)
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    # 唯一约束
    __table_args__ = (
        UniqueConstraint('dict_type', 'dict_code', name='uq_dict_type_code'),
    )
    
    # 关系 - 暂时移除自引用关系以避免SQLAlchemy错误
    # children = relationship("Dictionary", 
    #                       foreign_keys=[parent_code],
    #                       remote_side=[dict_code],
    #                       backref="parent")

class Asset(Base):
    """设备资产表 - 系统根实体"""
    __tablename__ = 'ASSET'
    
    asset_id = Column(String, primary_key=True)
    asset_type = Column(String, nullable=False)
    asset_name = Column(String, nullable=False)
    asset_code = Column(String, nullable=False)
    model = Column(String)
    device_composition = Column(Text)  # JSON数组存储设备组成关系
    cpu_cores = Column(Integer)
    memory_gb = Column(Float)
    storage_capacity = Column(Float)
    hard_disk_count = Column(Integer)
    hard_disk_specs = Column(String)
    department = Column(String)
    purpose_desc = Column(String)
    create_time = Column(DateTime, default=datetime.now)
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    # 关系
    os_systems = relationship("OperatingSystem", back_populates="asset", cascade="all, delete-orphan")
    oob_system = relationship("OobManagement", back_populates="asset", uselist=False, cascade="all, delete-orphan")
    business_systems = relationship("BusinessSystem", back_populates="asset")
    ips = relationship("IpManagement", back_populates="asset")

class OobManagement(Base):
    """带外管理系统表"""
    __tablename__ = 'OUT_OF_BAND'
    
    oob_id = Column(String, primary_key=True)
    asset_id = Column(String, ForeignKey('ASSET.asset_id', ondelete='CASCADE'), nullable=False)
    login_url = Column(String, nullable=False)
    create_time = Column(DateTime, default=datetime.now)
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    # 唯一约束
    __table_args__ = (
        UniqueConstraint('asset_id', name='uq_asset_oob'),
    )
    
    # 关系
    asset = relationship("Asset", back_populates="oob_system")
    # accounts = relationship("Account", back_populates="oob_system")
    ips = relationship("IpManagement", back_populates="oob_system")

class OperatingSystem(Base):
    """操作系统表"""
    __tablename__ = 'OS'
    
    os_id = Column(String, primary_key=True)
    asset_id = Column(String, ForeignKey('ASSET.asset_id', ondelete='CASCADE'), nullable=False)
    os_name = Column(String, nullable=False)
    version = Column(String, nullable=False)
    install_dir = Column(String)
    install_date = Column(Date)
    create_time = Column(DateTime, default=datetime.now)
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    # 关系
    asset = relationship("Asset", back_populates="os_systems")
    business_systems = relationship("BusinessSystem", back_populates="os")
    middlewares = relationship("Middleware", back_populates="os", cascade="all, delete-orphan")
    # accounts = relationship("Account", back_populates="os")
    ips = relationship("IpManagement", back_populates="os")

class BusinessSystem(Base):
    """业务系统表"""
    __tablename__ = 'SYSTEM'
    
    system_id = Column(String, primary_key=True)
    system_type = Column(String, nullable=False)
    system_name = Column(String, nullable=False)
    description = Column(String)
    负责人 = Column(String)
    department = Column(String)
    asset_id = Column(String, ForeignKey('ASSET.asset_id', ondelete='SET NULL'))
    os_id = Column(String, ForeignKey('OS.os_id', ondelete='SET NULL'))
    create_time = Column(DateTime, default=datetime.now)
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    # 检查约束 - 确保只能绑定到单一实体
    __table_args__ = (
        CheckConstraint(
            "(asset_id IS NOT NULL AND os_id IS NULL) OR "
            "(asset_id IS NULL AND os_id IS NOT NULL) OR "
            "(asset_id IS NULL AND os_id IS NULL)",
            name='chk_single_asset_or_os'
        ),
    )
    
    # 关系
    asset = relationship("Asset", back_populates="business_systems")
    os = relationship("OperatingSystem", back_populates="business_systems")
    middlewares = relationship("Middleware", secondary="SYSTEM_MIDDLEWARE", back_populates="systems")
    # accounts = relationship("Account", back_populates="system")
    ips = relationship("IpManagement", back_populates="system")

class Middleware(Base):
    """中间件表"""
    __tablename__ = 'MIDDLEWARE'
    
    mw_id = Column(String, primary_key=True)
    os_id = Column(String, ForeignKey('OS.os_id', ondelete='CASCADE'), nullable=False)
    mw_type = Column(String, nullable=False)
    name = Column(String, nullable=False)
    version = Column(String)
    install_dir = Column(String)
    port = Column(Integer)
    config_file = Column(String)
    create_time = Column(DateTime, default=datetime.now)
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    # 关系
    os = relationship("OperatingSystem", back_populates="middlewares")
    systems = relationship("BusinessSystem", secondary="SYSTEM_MIDDLEWARE", back_populates="middlewares")
    # accounts = relationship("Account", back_populates="middleware")
    ips = relationship("IpManagement", back_populates="middleware")

class SystemMiddleware(Base):
    """系统中间件关联表"""
    __tablename__ = 'SYSTEM_MIDDLEWARE'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    system_id = Column(String, ForeignKey('SYSTEM.system_id', ondelete='CASCADE'), nullable=False)
    mw_id = Column(String, ForeignKey('MIDDLEWARE.mw_id', ondelete='CASCADE'), nullable=False)
    create_time = Column(DateTime, default=datetime.now)
    
    # 唯一约束
    __table_args__ = (
        UniqueConstraint('system_id', 'mw_id', name='uq_system_middleware'),
    )

class IpManagement(Base):
    """IP管理表"""
    __tablename__ = 'IP'
    
    ip = Column(String, primary_key=True)
    subnet_mask = Column(String, nullable=False)
    vlan = Column(String, nullable=False)
    purpose = Column(String, nullable=False)
    status = Column(String, nullable=False)
    allocation_duration = Column(String, nullable=False)
    asset_id = Column(String, ForeignKey('ASSET.asset_id', ondelete='SET NULL'))
    os_id = Column(String, ForeignKey('OS.os_id', ondelete='SET NULL'))
    system_id = Column(String, ForeignKey('SYSTEM.system_id', ondelete='SET NULL'))
    mw_id = Column(String, ForeignKey('MIDDLEWARE.mw_id', ondelete='SET NULL'))
    oob_id = Column(String, ForeignKey('OUT_OF_BAND.oob_id', ondelete='SET NULL'))
    os_name = Column(String)
    hostname = Column(String)
    description = Column(String)
    create_time = Column(DateTime, default=datetime.now)
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    # 检查约束 - 确保IP只能绑定到单一实体
    __table_args__ = (
        CheckConstraint(
            "(asset_id IS NOT NULL AND os_id IS NULL AND system_id IS NULL AND mw_id IS NULL AND oob_id IS NULL) OR "
            "(asset_id IS NULL AND os_id IS NOT NULL AND system_id IS NULL AND mw_id IS NULL AND oob_id IS NULL) OR "
            "(asset_id IS NULL AND os_id IS NULL AND system_id IS NOT NULL AND mw_id IS NULL AND oob_id IS NULL) OR "
            "(asset_id IS NULL AND os_id IS NULL AND system_id IS NULL AND mw_id IS NOT NULL AND oob_id IS NULL) OR "
            "(asset_id IS NULL AND os_id IS NULL AND system_id IS NULL AND mw_id IS NULL AND oob_id IS NOT NULL)",
            name='chk_single_binding'
        ),
    )
    
    # 关系
    asset = relationship("Asset", back_populates="ips")
    os = relationship("OperatingSystem", back_populates="ips")
    system = relationship("BusinessSystem", back_populates="ips")
    middleware = relationship("Middleware", back_populates="ips")
    oob_system = relationship("OobManagement", back_populates="ips")

class Account(Base):
    """账号管理表"""
    __tablename__ = 'ACCOUNT'
    
    account_id = Column(String, primary_key=True)
    relation_type = Column(String, nullable=False)
    relation_id = Column(String, nullable=False)
    username = Column(String, nullable=False)
    encrypted_password = Column(String, nullable=False)
    role = Column(String)
    valid_from = Column(Date)
    valid_to = Column(Date)
    description = Column(String)
    create_time = Column(DateTime, default=datetime.now)
    update_time = Column(DateTime, default=datetime.now, onupdate=datetime.now)
    
    # 关系 - 暂时移除复杂的多态关联以避免SQLAlchemy错误
    # oob_system = relationship("OobManagement", 
    #                          foreign_keys="[relation_id]",
    #                          primaryjoin="and_(Account.relation_type=='OOB', Account.relation_id==OobManagement.oob_id)",
    #                          back_populates="accounts")
    # 
    # os = relationship("OperatingSystem",
    #                  foreign_keys="[relation_id]", 
    #                  primaryjoin="and_(Account.relation_type=='OS', Account.relation_id==OperatingSystem.os_id)",
    #                  back_populates="accounts")
    # 
    # system = relationship("BusinessSystem",
    #                      foreign_keys="[relation_id]",
    #                      primaryjoin="and_(Account.relation_type=='SYSTEM', Account.relation_id==BusinessSystem.system_id)",
    #                      back_populates="accounts")
    # 
    # middleware = relationship("Middleware",
    #                          foreign_keys="[relation_id]",
    #                          primaryjoin="and_(Account.relation_type=='MIDDLEWARE', Account.relation_id==Middleware.mw_id)",
    #                          back_populates="accounts")